<?php

namespace Wintel\RestBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;

class ReportController extends Controller
{
    /**
     * @var array
     * 呼叫类型
     */
    private $callType = array(
        '1' => '呼出',
        '2' => '呼入',
        '3' => '呼出转接',
        '4' => '呼入转接',
        '5' => '呼出拦截',
        '6' => '呼入拦截',
        '7' => '被咨询',
        '9' => '监听',
    );

    /**
     * @var array
     * 呼叫结果
     */
    private $callResult = array(
        '0' => '接通',
        '1' => '振铃放弃',
        '2' => '未接',
    );

    /**
     * @var array
     * 结果类型
     */
    private $endResult = array(
        '11' => '用户挂断',
        '12' => '坐席挂断',
    );

    /**
     * @var array
     * 漏话原因
     */
    private $reason = array(
        '1' => 'IVR超限',
        '2' => '未启用',
        '3' => '过期',
        '4' => '余额不足',
        '5' => '不在接通时间',
        '6' => '未设置日程',
        '7' => '未找到企业',
        '8' => '黑名单',
        '9' => '非白名单号码',
        '101' => 'IVR挂机',
        '102' => '留言',
        '103' => '未接通',
        '104' => '未接通留言',
        '109' => '非白名单号码',
    );

    /**
     * @var array
     * 数据处理状态
     */
    private $cdrStatus = array(
        '0' => '待分配',
        '1' => '已分配',
        '2' => '处理中',
        '3' => '已完成',
    );

    /**
     * @var array
     *  评价结果
     */
    private $evaluate = array(
        '-1' => '客户挂机',
        '-2' => '超时未评价',
        '-3' => '未评价挂机',
        '-4' => '坐席挂机',
        '1' => '1',
        '2' => '2',
        '3' => '3',
        '4' => '4',
        '5' => '5',
        '6' => '6',
    );
    /**
     * @param Request $request
     * @return JsonResponse
     * 获取坐席工作表现报表（半小时）
     */
    public function agentHalfhourAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }
        $pageInfo = $this->getAgentPage($addInfo, $vid, array('ag_num', 'start_date'));
        if (isset($pageInfo['msg'])) {
            return new JsonResponse($pageInfo['msg']);
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_agent_halfhour '.
            'WHERE '.$pageInfo['where'],
            $pageInfo['params']
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_agent_halfhour', $addInfo);
        //ok
        $list = $conn->fetchAll(
            'SELECT ag_id,ag_num,ag_name,in_num,out_num,conn_secs,internal_num,ring_num,ring_secs,consult_num,'.
            'consult_secs,hold_num,hold_secs,conference_secs,shift_num,login_secs,ready_secs,busy_secs,wait_num,'.
            'wait_secs,conference_num,start_date,time_stamp '.
            'FROM rep_agent_halfhour '.
            'WHERE '.$pageInfo['where'].
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $pageInfo['params']
        );

        $result = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $hour = floor($v['time_stamp']/2);
                $minute = ($v['time_stamp']/2)==1 ? '00' : '30';
                $nowdate = $v['start_date'].' '.$hour.':'.$minute;
                $v['nowdate'] = $nowdate;
                unset($v['start_date']);
                unset($v['time_stamp']);
                $result[] = $v;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$result);
        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 获取坐席工作表现报表（小时）
     */
    public function agentHourAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }
        $pageInfo = $this->getAgentPage($addInfo, $vid, array('ag_num', 'start_date'));
        if (isset($pageInfo['msg'])) {
            return new JsonResponse($pageInfo['msg']);
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_agent_hour '.
            'WHERE '.$pageInfo['where'],
            $pageInfo['params']
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_agent_hour', $addInfo);

        //ok
        $list = $conn->fetchAll(
            'SELECT ag_id,ag_num,ag_name,in_num,out_num,conn_secs,internal_num,ring_num,ring_secs,consult_num,'.
            'consult_secs,hold_num,hold_secs,conference_num,conference_secs,shift_num,login_secs,ready_secs,'.
            'busy_secs,wait_num,wait_secs,start_date,time_stamp '.
            'FROM rep_agent_hour '.
            'WHERE '.$pageInfo['where'].
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $pageInfo['params']
        );

        $result = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $nowdate = $v['start_date'].' '.$v['time_stamp'];
                $v['nowdate'] = $nowdate;
                unset($v['start_date']);
                unset($v['time_stamp']);
                $result[] = $v;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$result);
        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 坐席工作表现报表（天）
     */
    public function agentDayAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }
        $pageInfo = $this->getAgentPage($addInfo, $vid, array('ag_num', 'nowdate'));
        if (isset($pageInfo['msg'])) {
            return new JsonResponse($pageInfo['msg']);
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_agent_day '.
            'WHERE '.$pageInfo['where'],
            $pageInfo['params']
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_agent_day', $addInfo);
        //ok
        $list = $conn->fetchAll(
            'SELECT ag_id,ag_num,ag_name,in_num,out_num,conn_secs,internal_num,ring_num,ring_secs,consult_num,'.
            'consult_secs,hold_num,hold_secs,conference_num,conference_secs,shift_num,login_secs,ready_secs,'.
            'busy_secs,wait_num,wait_secs,nowdate '.
            'FROM rep_agent_day '.
            'WHERE '.$pageInfo['where'].
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $pageInfo['params']
        );
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$list);
        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 坐席工作表现报表（月）
     */
    public function agentMonthAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/";

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }

        $where = 'vcc_id = :vcc_id ';
        $params = array('vcc_id' => $vid);
        if (isset($addInfo['filter'])) {
            //坐席工号；
            $where.= isset($addInfo['filter']['ag_num']) && !empty($addInfo['filter']['ag_num']) ?
                " AND ag_num LIKE '%".$addInfo['filter']['ag_num']."%' " : '';

            //开始时间
            if (isset($addInfo['filter']['start_date']) && !empty($addInfo['filter']['start_date'])) {
                $start_date = $addInfo['filter']['start_date'];
                $msg = $this->get('validator.extend.custom')->regexRormat($reg, $start_date, '开始日期不正确', 404);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND nowmonth >= :start_date ";
                $params['start_date'] = $start_date;
            }

            //结束时间
            if (isset($addInfo['filter']['end_date']) && !empty($addInfo['filter']['end_date'])) {
                $end_date = $addInfo['filter']['end_date'];
                $msg = $this->get('validator.extend.custom')->regexRormat($reg, $end_date, '结束日期不正确', 405);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND nowmonth <= :end_date ";
                $params['end_date'] = $end_date;
            }
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_agent_month '.
            'WHERE '.$where,
            $params
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_agent_month', $addInfo);
        //ok
        $list = $conn->fetchAll(
            'SELECT ag_id,ag_num,ag_name,in_num,out_num,conn_secs,internal_num,ring_num,ring_secs,consult_num,'.
            'consult_secs,hold_num,hold_secs,conference_num,conference_secs,shift_num,login_secs,ready_secs,'.
            'busy_secs,wait_num,wait_secs,nowmonth '.
            'FROM rep_agent_month '.
            'WHERE '.$where.
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$list);
        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     *  获取技能组话务报表（半小时）
     */
    public function queueHalfhourAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }
        $pageInfo = $this->getAgentPage($addInfo, $vid, array(array('int','queue_id'), 'start_date'));
        if (isset($pageInfo['msg'])) {
            return new JsonResponse($pageInfo['msg']);
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_queue_halfhour '.
            'WHERE '.$pageInfo['where'],
            $pageInfo['params']
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_queue_halfhour', $addInfo);

        //ok
        $list = $conn->fetchAll(
            'SELECT queue_id,queue_name,in_num,lost_num,lost_secs,conn_num,queue_secs,ring_secs,conn5_num,'.
            'conn10_num,conn15_num,conn20_num,conn30_num,conn_secs,wait_secs,deal_secs,time_stamp,start_date '.
            'FROM rep_queue_halfhour '.
            'WHERE '.$pageInfo['where'].
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $pageInfo['params']
        );

        $result = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $hour = floor($v['time_stamp']/2);
                $minute = ($v['time_stamp']/2)==1 ? '00' : '30';
                $nowdate = $v['start_date'].' '.$hour.':'.$minute;
                $v['nowdate'] = $nowdate;
                $v['conn_rate'] = $v['in_num'] > 0 ? round($v['conn_num']/$v['in_num']*100, 2)."%" : "0%";
                unset($v['start_date']);
                unset($v['time_stamp']);
                $result[] = $v;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$result);
        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 获取技能组话务报表（小时）
     */
    public function queueHourAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }
        $pageInfo = $this->getAgentPage($addInfo, $vid, array(array('int','queue_id'), 'start_date'));
        if (isset($pageInfo['msg'])) {
            return new JsonResponse($pageInfo['msg']);
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_queue_hour '.
            'WHERE '.$pageInfo['where'],
            $pageInfo['params']
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_queue_hour', $addInfo);
        //ok
        $list = $conn->fetchAll(
            'SELECT queue_id,queue_name,in_num,lost_num,lost_secs,conn_num,queue_secs,ring_secs,conn5_num,'.
            'conn10_num,conn15_num,conn20_num,conn30_num,conn_secs,wait_secs,deal_secs,time_stamp,start_date '.
            'FROM rep_queue_hour '.
            'WHERE '.$pageInfo['where'].
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $pageInfo['params']
        );

        $result = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $nowdate = $v['start_date'].' '.$v['time_stamp'];
                $v['nowdate'] = $nowdate;
                $v['conn_rate'] = $v['in_num'] > 0 ? round($v['conn_num']/$v['in_num']*100, 2)."%" : "0%";
                unset($v['start_date']);
                unset($v['time_stamp']);
                $result[] = $v;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$result);
        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 获取技能组话务报表（天）
     */
    public function queueDayAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }
        $pageInfo = $this->getAgentPage($addInfo, $vid, array(array('int','queue_id'), 'nowdate'));
        if (isset($pageInfo['msg'])) {
            return new JsonResponse($pageInfo['msg']);
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_queue_day '.
            'WHERE '.$pageInfo['where'],
            $pageInfo['params']
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_queue_day', $addInfo);

        //ok
        $list = $conn->fetchAll(
            'SELECT queue_id,queue_name,in_num,lost_num,lost_secs,conn_num,queue_secs,ring_secs,conn5_num,'.
            'conn10_num,conn15_num,conn20_num,conn30_num,conn_secs,wait_secs,deal_secs,nowdate '.
            'FROM rep_queue_day '.
            'WHERE '.$pageInfo['where'].
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $pageInfo['params']
        );

        $result = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $v['conn_rate'] = $v['in_num'] > 0 ? round($v['conn_num']/$v['in_num']*100, 2)."%" : "0%";
                $result[] = $v;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$result);
        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 获取技能组话务报表（月）
     */
    public function queueMonthAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/";

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }

        $where = 'vcc_id = :vcc_id ';
        $params = array('vcc_id' => $vid);
        if (isset($addInfo['filter'])) {
            //技能组ID；
            if (isset($addInfo['filter']['queue_id']) && !empty($addInfo['filter']['queue_id'])) {
                $where.= 'AND queue_id = :queue_id ';
                $params['queue_id'] = $addInfo['filter']['queue_id'];
            }

            //开始时间
            if (isset($addInfo['filter']['start_date']) && !empty($addInfo['filter']['start_date'])) {
                $start_date = $addInfo['filter']['start_date'];
                $msg = $this->get('validator.extend.custom')->regexRormat($reg, $start_date, '开始日期不正确', 404);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND nowmonth >= :start_date ";
                $params['start_date'] = $start_date;
            }

            //结束时间
            if (isset($addInfo['filter']['end_date']) && !empty($addInfo['filter']['end_date'])) {
                $end_date = $addInfo['filter']['end_date'];
                $msg = $this->get('validator.extend.custom')->regexRormat($reg, $end_date, '结束日期不正确', 405);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND nowmonth <= :end_date ";
                $params['end_date'] = $end_date;
            }
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_queue_month '.
            'WHERE '.$where,
            $params
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_queue_month', $addInfo);
        //ok
        $list = $conn->fetchAll(
            'SELECT queue_id,queue_name,in_num,lost_num,lost_secs,conn_num,queue_secs,ring_secs,conn5_num,'.
            'conn10_num,conn15_num,conn20_num,conn30_num,conn_secs,wait_secs,deal_secs,nowmonth '.
            'FROM rep_queue_month '.
            'WHERE '.$where.
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );

        $result = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $v['conn_rate'] = $v['in_num'] > 0 ? round($v['conn_num']/$v['in_num']*100, 2)."%" : "0%";
                $result[] = $v;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$result);
        return new JsonResponse($ret);
    }

    /**
     * 呼叫中心整体话务报表(天)
     *
     * @param Request $request
     * @return JsonResponse
     */
    public function systemDayAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code = $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }
        $pageInfo = $this->getAgentPage($addInfo, $vid, array('', 'nowdate'));
        if (isset($pageInfo['msg'])) {
            return new JsonResponse($pageInfo['msg']);
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_system_day '.
            'WHERE '.$pageInfo['where'],
            $pageInfo['params']
        );

        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_system_day', $addInfo);
        $result = $conn->fetchAll(
            "SELECT nowdate, ivr_num as ivr_num, in_num, lost_num, lost_secs, conn_num,conn_num/in_num as conn_rate,".
            "queue_secs, ring_secs, conn5_num, conn10_num, conn15_num, conn20_num, conn30_num, login_secs, conn_secs,".
            "wait_secs, deal_secs ".
            'FROM rep_system_day '.
            'WHERE '.$pageInfo['where'].
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $pageInfo['params']
        );
        $ret = array('code' => 200, 'message' => 'ok', 'total'=>$count, 'data' => $result);

        return new JsonResponse($ret);
    }

    /**
     * 呼叫中心整体话务报表(小时)
     *
     * @param Request $request
     * @return JsonResponse
     */
    public function systemHourAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code = $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }
        $pageInfo = $this->getAgentPage($addInfo, $vid, array('', 'start_date'));
        if (isset($pageInfo['msg'])) {
            return new JsonResponse($pageInfo['msg']);
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_system_hour '.
            'WHERE '.$pageInfo['where'],
            $pageInfo['params']
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_system_hour', $addInfo);
        $result = $conn->fetchAll(
            "SELECT CONCAT(start_date,' ',time_stamp) as nowdate, ivr_num as ivr_num, in_num, lost_num,".
            "lost_secs, conn_num, conn_num/in_num as conn_rate, queue_secs, ring_secs, conn5_num, ".
            "conn10_num, conn15_num, conn20_num, conn30_num, login_secs, conn_secs, wait_secs, deal_secs".
            " FROM rep_system_hour ".
            'WHERE '.$pageInfo['where'].
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $pageInfo['params']
        );
        $ret = array('code' => 200, 'message' => 'ok', 'total'=>$count, 'data' => $result);

        return new JsonResponse($ret);
    }

    /**
     * 呼叫中心整体话务报表(半小时)
     *
     * @param Request $request
     * @return JsonResponse
     */
    public function systemHalfHourAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code = $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }
        $pageInfo = $this->getAgentPage($addInfo, $vid, array('', 'start_date'));
        if (isset($pageInfo['msg'])) {
            return new JsonResponse($pageInfo['msg']);
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_system_halfhour '.
            'WHERE '.$pageInfo['where'],
            $pageInfo['params']
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_system_halfhour', $addInfo);

        $result = $conn->fetchAll(
            "SELECT CONCAT(start_date, ' ', CONCAT(floor(time_stamp/2),':',CASE WHEN time_stamp MOD 2 = 1 ".
            "THEN '30' ELSE '00' END)) as nowdate, ivr_num as ivr_num, in_num, lost_num, lost_secs, conn_num,".
            "conn_num/in_num as conn_rate, queue_secs, ring_secs, conn5_num, conn10_num, conn15_num, conn20_num,".
            "conn30_num, login_secs, conn_secs, wait_secs, deal_secs ".
            "FROM rep_system_halfhour ".
            'WHERE '.$pageInfo['where'].
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $pageInfo['params']
        );
        $ret = array('code' => 200, 'message' => 'ok', 'total'=>$count, 'data' => $result);

        return new JsonResponse($ret);
    }

    /**
     * 呼叫中心整体话务报表(月)
     *
     * @param Request $request
     * @return JsonResponse
     */
    public function systemMonthAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code = $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/";

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }

        $where = 'vcc_id = :vcc_id ';
        $params = array('vcc_id' => $vid);
        if (isset($addInfo['filter'])) {
            //开始时间
            if (isset($addInfo['filter']['start_date']) && !empty($addInfo['filter']['start_date'])) {
                $start_date = $addInfo['filter']['start_date'];
                $msg = $this->get('validator.extend.custom')->regexRormat($reg, $start_date, '开始日期不正确', 404);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND nowmonth >= :start_date ";
                $params['start_date'] = $start_date;
            }

            //结束时间
            if (isset($addInfo['filter']['end_date']) && !empty($addInfo['filter']['end_date'])) {
                $end_date = $addInfo['filter']['end_date'];
                $msg = $this->get('validator.extend.custom')->regexRormat($reg, $end_date, '结束日期不正确', 405);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND nowmonth <= :end_date ";
                $params['end_date'] = $end_date;
            }
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM rep_system_month '.
            'WHERE '.$where,
            $params
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_system_month', $addInfo);

        $result = $conn->fetchAll(
            "SELECT nowmonth as nowdate, ivr_num as ivr_num, in_num, lost_num, lost_secs, conn_num, ".
            "conn_num/in_num as conn_rate, queue_secs, ring_secs, conn5_num, conn10_num, conn15_num, ".
            "conn20_num, conn30_num, login_secs, conn_secs, wait_secs, deal_secs FROM rep_system_month ".
            'WHERE '.$where.
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );
        $ret = array('code' => 200, 'message' => 'ok', 'total'=>$count, 'data' => $result);

        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 坐席通话明细报表
     */
    public function callDetailAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }

        $where = '';
        $params = array();
        if (isset($addInfo['filter'])) {
            //坐席工号；
            $where.= isset($addInfo['filter']['ag_num']) && !empty($addInfo['filter']['ag_num']) ?
                " AND ag_num LIKE '%".$addInfo['filter']['ag_num']."%' " : '';

            //客户号码
            $where.= isset($addInfo['filter']['cus_phone']) && !empty($addInfo['filter']['cus_phone']) ?
                " AND cus_phone LIKE '%".$addInfo['filter']['cus_phone']."%' " : '';

            //坐席号码
            $where.= isset($addInfo['filter']['ag_phone']) && !empty($addInfo['filter']['ag_phone']) ?
                " AND ag_phone LIKE '%".$addInfo['filter']['ag_phone']."%' " : '';

            //呼叫结果
            if (isset($addInfo['filter']['call_result']) && $addInfo['filter']['call_result'] !== '') {
                $call_result = (int)$addInfo['filter']['call_result'] ;
                $where.=" AND result = :call_result ";
                $params['call_result'] = $call_result;
            }

            //结束类型
            if (isset($addInfo['filter']['end_reason']) && $addInfo['filter']['end_reason'] !== '') {
                $end_reason = (int)$addInfo['filter']['end_reason'] ;
                $where.=" AND endresult = :end_reason ";
                $params['end_reason'] = $end_reason;
            }

            //呼叫类型
            if (isset($addInfo['filter']['call_type']) && $addInfo['filter']['call_type'] !== false) {
                $call_type = (int)$addInfo['filter']['call_type'] ;
                $where.=" AND call_type = :call_type ";
                $params['call_type'] = $call_type;
            }

            //技能组
            if (isset($addInfo['filter']['que_id']) && !empty($addInfo['filter']['que_id'])) {
                $que_id = (int)$addInfo['filter']['que_id'] ;
                $msg = $this->get('validator.custom')->vccQue($vid, $que_id, 406);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.=" AND que_id = :que_id ";
                $params['que_id'] = $que_id;
            }

            //开始时间
            if (isset($addInfo['filter']['start_time']) && !empty($addInfo['filter']['start_time'])) {
                $start_time = $addInfo['filter']['start_time'];
                $msg = $this->get('validator.extend.custom')->isDateTime('开始时间格式不正确', $start_time, 404);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND start_time >= UNIX_TIMESTAMP('$start_time')";
            }

            //结束时间
            if (isset($addInfo['filter']['end_time']) && !empty($addInfo['filter']['end_time'])) {
                $end_time = $addInfo['filter']['end_time'];
                $msg = $this->get('validator.extend.custom')->isDateTime('结束时间格式不正确', $end_time, 405);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND start_time <= UNIX_TIMESTAMP('$end_time')";
            }
        }

        $params['vcc_id'] = $vid;
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM win_agcdr '.
            'WHERE vcc_id = :vcc_id '.$where,
            $params
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'win_agcdr', $addInfo);
        $list = $conn->fetchAll(
            'SELECT ag_num,ag_name,que_name,ag_phone,cus_phone,call_type,call_id,'.
            'start_time,end_time,conn_secs,result,endresult '.
            'FROM win_agcdr '.
            "WHERE vcc_id = :vcc_id ".$where.
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );
        $res = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $v['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : '';
                $v['end_time'] = $v['end_time'] ? date("Y-m-d H:i:s", $v['end_time']) : '';
                $v['call_type'] = isset($this->callType[$v['call_type']]) ? $this->callType[$v['call_type']] : '';
                $v['result'] = isset($this->callResult[$v['result']]) ? $this->callResult[$v['result']] : '';
                $v['endresult'] = isset($this->endResult[$v['endresult']]) ? $this->endResult[$v['endresult']] : '其他';
                $res[] = $v;
            }
        }
        return new JsonResponse(array('code'=>'200','message'=>'ok','total'=>$count,'data'=>$res));
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 获取漏话明细报表；
     */
    public function getLostAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");

        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }

        $where = '';
        $params = array();
        if (isset($addInfo['filter'])) {
            //主叫号码；
            $where.= isset($addInfo['filter']['caller']) && !empty($addInfo['filter']['caller']) ?
                " AND caller LIKE '%".$addInfo['filter']['caller']."%' " : '';
            //开始时间
            if (isset($addInfo['filter']['start_time']) && !empty($addInfo['filter']['start_time'])) {
                $start_time = $addInfo['filter']['start_time'];
                $msg = $this->get('validator.extend.custom')->isDateTime('开始时间格式不正确', $start_time, 404);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND start_time >= UNIX_TIMESTAMP('$start_time')";
            }
            //结束时间
            if (isset($addInfo['filter']['end_time']) && !empty($addInfo['filter']['end_time'])) {
                $end_time = $addInfo['filter']['end_time'];
                $msg = $this->get('validator.extend.custom')->isDateTime('结束时间格式不正确', $end_time, 405);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND start_time <= UNIX_TIMESTAMP('$end_time')";
            }
            //是否工作时间；
            if (isset($addInfo['filter']['if_work'])) {
                $if_work= $addInfo['filter']['if_work'];
//                echo $if_work;
//                if ($if_work != 0 && $if_work != 1) {
//                    echo "yyy";
//                    return new JsonResponse(array('code'=>407,'message'=>'if_work字段只能为0或1'));
//                }
                $where.="AND if_work = '$if_work' ";
            }
            //是否接通；
            if (isset($addInfo['filter']['if_trans'])) {
                $if_trans = $addInfo['filter']['if_trans'];
//                if ($if_trans != 0 && $if_trans != 1) {
//                    return new JsonResponse(array('code'=>408,'message'=>'if_trans值只能为0或1'));
//                }
                $where.="AND if_trans = '$if_trans' ";
            }
            //中继号码；
            if (isset($addInfo['filter']['server_num']) && !empty($addInfo['filter']['server_num'])) {
                $server_num = $addInfo['filter']['server_num'];
                $msg = $this->get('validator.extend.custom')->isNumeric('server_num', $server_num, 409);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND server_num = :server_num ";
                $params['server_num'] = $server_num;
            }
        }
        $params['vcc_id'] = $vcc_id;
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM win_lost_cdr '.
            'WHERE vcc_id = :vcc_id '.$where,
            $params
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'win_lost_cdr', $addInfo);
        $list = $conn->fetchAll(
            "SELECT caller,charge_user,cdr_status,ag_num,que_name,server_num,start_time,server_num,reason,if_work,if_trans ".
            "FROM win_lost_cdr ".
            "WHERE vcc_id = :vcc_id ".$where.
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );
        $res = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $v['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : '';
                $v['reason'] = isset($this->reason[$v['reason']]) ? $this->reason[$v['reason']] : '';
                $v['cdr_status'] = isset($this->cdrStatus[$v['cdr_status']]) ? $this->cdrStatus[$v['cdr_status']] : '';
                $res[] = $v;
            }
        }
        return new JsonResponse(array('code'=>'200','message'=>'ok','total'=>$count,'data'=>$res));
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 技能组来电分配报表
     */
    public function inallotAction(Request $request)
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $vcc_code =  $request->get('vcc_code', 0);
        $info = $request->get('info', ''); //分页搜索相关信息；

        //vcc_code 验证
        $msg = $vid = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //分页搜索相关信息；
        $addInfo = array();
        if (!empty($info)) {
            $addInfo = json_decode($info, true);
            if (json_last_error()) {
                return new JsonResponse(array('code' => 403, 'message'=>'info格式非json'));
            }
        }

        $where = 'vcc_id = :vcc_id ';
        $params = array('vcc_id' => $vid);
        if (isset($addInfo['filter'])) {
            //呼叫结果；
            if (isset($addInfo['filter']['result']) && $addInfo['filter']['result'] !== '') {
                $result = (int)$addInfo['filter']['result'];
                $where.=" AND result = :result ";
                $params['result'] = $result;
            }

            //开始时间
            if (isset($addInfo['filter']['start_time']) && !empty($addInfo['filter']['start_time'])) {
                $start_time = $addInfo['filter']['start_time'];
                $msg = $this->get('validator.extend.custom')->isDateTime('开始时间格式不正确', $start_time, 404);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND start_time >= :start_time ";
                $params['start_time'] = strtotime($start_time);
            }

            //结束时间
            if (isset($addInfo['filter']['end_time']) && !empty($addInfo['filter']['end_time'])) {
                $end_time = $addInfo['filter']['end_time'];
                $msg = $this->get('validator.extend.custom')->isDateTime('结束时间格式不正确', $end_time, 405);
                if (!empty($msg) && is_array($msg)) {
                    return new JsonResponse($msg);
                }
                $where.="AND end_time <= :end_time ";
                $params['end_time'] = strtotime($end_time);
            }
        }
        //查出未删除的技能组
        $que_id = $conn->fetchAll(
            'SELECT id '.
            'FROM win_queue '.
            'WHERE is_del = :is_del AND vcc_id = :vcc_id ',
            array('is_del'=>0, 'vcc_id'=>$vid)
        );
        $que_row = array();
        if (!empty($que_id)) {
            foreach ($que_id as $v) {
                $que_row[] = $v['id'];
            }
        }
        $where.= !empty($que_row) ? ' AND que_id IN ('.implode(',', $que_row).') ' : '';
        $count = $conn->fetchAll(
            'SELECT count(*) '.
            'FROM win_incdr '.
            'WHERE '.$where.' GROUP BY que_id ',
            $params
        );
        $count = count($count);
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'win_incdr', $addInfo);
        //总条数，不按照 que_id 分组
        $sum = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM win_incdr '.
            'WHERE '.$where,
            $params
        );
        $list = $conn->fetchAll(
            'SELECT que_name,count(*) AS num '.
            'FROM win_incdr '.
            'WHERE '.$where.
            'GROUP BY que_id ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );

        $res = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $v['rate'] = empty($sum) ? '0.00%' : number_format($v['num']/$sum * 100, 2) . '%' ;
                $res[] = $v;
            }
        }
        return new JsonResponse(array('code'=>'200','message'=>'ok','total'=>$count,'data'=>$res));
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 满意度评价报表；
     */
    public function evaluateAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");

        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //403 json
        $msg = $addInfo = $this->get('validator.custom')->checkJson($info);
        if (!empty($msg) && isset($msg['code'])) {
            return new JsonResponse($msg);
        }
        $where = 'vcc_id = :vcc_id AND result = 0 ';
        $params =  array('vcc_id' => $vcc_id);
        if (isset($addInfo['filter'])) {
            //坐席工号，发起号码，被监听号码
            $where.=$this->get('help.function')->likeWhere(array('ag_num','ag_phone','cus_phone'), $addInfo['filter']);
            //时间，需要验证格式；
            $msg = $this->get('help.function')->dateTimeWhere($addInfo['filter']);
            if (!empty($msg) && is_array($msg)) {
                return new JsonResponse($msg);
            } else {
                $where.=$msg;
            }
            //技能组,评价结果
            $enRow = $this->get('help.function')->equalWhere(array('que_id','evaluate'), $addInfo['filter']);
            $params = array_merge($enRow['params'], $params);
            $where.=$enRow['where'];
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM win_agcdr '.
            'WHERE '.$where,
            $params
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'win_agcdr', $addInfo);
        $list = $conn->fetchAll(
            "SELECT ag_id,ag_num,ag_name,ag_phone,cus_phone,serv_num,que_name,call_type,start_time,conn_secs,evaluate,".
            'call_id,record_mark '.
            "FROM win_agcdr ".
            "WHERE ".$where.
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );
        $res = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $v['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : "";
                $v['call_type'] = isset($this->callType[$v['call_type']]) ? $this->callType[$v['call_type']] : '';
                $v['evaluate_value'] = $v['evaluate']; //用户汇总；
                $v['evaluate'] = isset($this->evaluate[$v['evaluate']]) ? $this->evaluate[$v['evaluate']] : '';
                $res[] = $v;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$res);
        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 技能组转移明细报表
     */
    public function transAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");

        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //403 json
        $msg = $addInfo = $this->get('validator.custom')->checkJson($info);
        if (!empty($msg) && isset($msg['code'])) {
            return new JsonResponse($msg);
        }
        $where = 'vcc_id = :vcc_id ';
        $params =  array('vcc_id' => $vcc_id);
        if (isset($addInfo['filter'])) {
            //坐席工号，坐席号码，客户号码
            $where.=$this->get('help.function')->likeWhere(array('ag_num','ag_phone','cus_phone'), $addInfo['filter']);
            //时间，需要验证格式；
            $msg = $this->get('help.function')->dateTimeWhere($addInfo['filter']);
            if (!empty($msg) && is_array($msg)) {
                return new JsonResponse($msg);
            } else {
                $where.=$msg;
            }
            //技能组,转移技能组id
            $enRow = $this->get('help.function')->equalWhere(array('que_id','que_id_trans'), $addInfo['filter']);
            $params = array_merge($enRow['params'], $params);
            $where.=$enRow['where'];
        }
        $count = $conn->fetchColumn(
            'SELECT count(*) '.
            'FROM win_trans_call '.
            'WHERE '.$where,
            $params
        );
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'win_trans_call', $addInfo);
        $list = $conn->fetchAll(
            "SELECT ag_name,id,ag_num,ag_phone,cus_phone,que_name,que_name_trans,start_time ".
            "FROM win_trans_call ".
            "WHERE ".$where.
            'ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );
        $res = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $v['start_time'] = $v['start_time'] ? date("Y-m-d H:i:s", $v['start_time']) : "";
                $res[] = $v;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$res);
        return new JsonResponse($ret);

    }

    /**
     * @param $addInfo
     * @param $vcc_id
     * @param $field (第一个元素表示搜素字段，第二个时间字段)
     * @return array|JsonResponse
     * 返回分页信息
     */
    private function getAgentPage($addInfo, $vcc_id, $field)
    {
        $where = 'vcc_id = :vcc_id ';
        $params = array();
        if (isset($addInfo['filter'])) {
            //坐席工号；
            if (is_array($field[0])) {
                switch ($field[0][0]) {
                    case 'int':
                        if (isset($addInfo['filter'][$field[0][1]]) && !empty($addInfo['filter'][$field[0][1]])) {
                            $intValue = $addInfo['filter'][$field[0][1]];
                            $where.='AND '.$field[0][1].'= :intValue ';
                            $params['intValue'] = $intValue;
                        }
                        break;
                }
            } else {
                $where.= isset($addInfo['filter'][$field[0]]) && !empty($addInfo['filter'][$field[0]]) ?
                    " AND $field[0] LIKE '%".$addInfo['filter'][$field[0]]."%' " : '';
            }

            //开始时间
            if (isset($addInfo['filter']['start_date']) && !empty($addInfo['filter']['start_date'])) {
                $start_date = $addInfo['filter']['start_date'];
                $msg = $this->get('validator.extend.custom')->isDate('开始时间格式不正确', $start_date, 404);
                if (!empty($msg) && is_array($msg)) {
                    return array('msg'=>$msg);
                }
                $where.="AND $field[1] >= :start_date ";
                $params['start_date'] = $start_date;
            }

            //结束时间
            if (isset($addInfo['filter']['end_date']) && !empty($addInfo['filter']['end_date'])) {
                $end_date = $addInfo['filter']['end_date'];
                $msg = $this->get('validator.extend.custom')->isDate('结束时间格式不正确', $end_date, 405);
                if (!empty($msg) && is_array($msg)) {
                    return array('msg'=>$msg);
                }
                $where.="AND $field[1] <= :end_date ";
                $params['end_date'] = $end_date;
            }
        }
        $params['vcc_id'] = $vcc_id;
        return array('where' => $where,'params' => $params);
    }
}
